<?php
    class StudentDormitoryChangeDAL{

        public function getEmptyDormitoryList($instructorID,$apartment,$search,$page,$pageSize){
            $query_page='select FOUND_ROWS() as count;';
            $query="select SQL_CALC_FOUND_ROWS tb_instructor_dormitory_allocating_list.apartment_id,
            tb_apartment_info.apartment_name,tb_instructor_dormitory_allocating_list.floor,
            tb_instructor_dormitory_allocating_list.dormitory_id,
            tb_dormitory_info.property,tb_instructor_dormitory_allocating_list.beds 
            from tb_instructor_info,tb_instructor_dormitory_allocating_list,tb_apartment_info,tb_dormitory_info 
            where tb_instructor_dormitory_allocating_list.grade=tb_instructor_info.grade 
            and tb_instructor_dormitory_allocating_list.major_id=tb_instructor_info.major_id 
            and tb_apartment_info.apartment_id=tb_instructor_dormitory_allocating_list.apartment_id 
            and tb_dormitory_info.apartment_id=tb_instructor_dormitory_allocating_list.apartment_id 
            and tb_dormitory_info.dormitory_id=tb_instructor_dormitory_allocating_list.dormitory_id 
            and tb_instructor_info.status=1 
            and tb_apartment_info.status=1 
            and tb_dormitory_info.status=1 
            and tb_instructor_dormitory_allocating_list.status=1 
            and (select count(1) as count from tb_student_dormitory_roster 
            where tb_student_dormitory_roster.apartment_id=tb_instructor_dormitory_allocating_list.apartment_id 
            and tb_student_dormitory_roster.status=1)<tb_instructor_dormitory_allocating_list.beds 
            and tb_instructor_info.uid=:uid 
            and tb_instructor_dormitory_allocating_list.apartment_id=:apartment_id ".
            ($search==''?'':'and tb_instructor_dormitory_allocating_list.dormitory_id=:dormitory_id')." 
            order by tb_instructor_dormitory_allocating_list.apartment_id,tb_instructor_dormitory_allocating_list.floor, 
            tb_instructor_dormitory_allocating_list.dormitory_id LIMIT :page,:pageSize;;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorID);
            $statement->bindValue(":apartment_id",$apartment);
            if(!$search==''){
                $statement->bindValue(":dormitory_id",$search);
            }
            $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
            if($statement->execute()){
                $dormitoryList=$statement->fetchALL(PDO::FETCH_ASSOC);

                $row=$pdo->query($query_page);
                $count=$row->fetch(PDO::FETCH_ASSOC);
                $row->closeCursor();

                $result=array(
                    'dormitoryList'=>$dormitoryList,
                    'count'=>$count['count']
                );
            }
            
            $statement->closeCursor();

            return $result;
        }

        public function exchangeStudentDormitory($student_id,$apartment_id,$floor,$dormitory_id){
            $query_exchange="update tb_student_dormitory_roster set 
            apartment_id=:apartment_id,floor=:floor,dormitory_id=:dormitory_id 
            where student_id=:student_id;";

            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();

                $statement=$pdo->prepare($query_exchange,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));

                $statement->bindValue(':apartment_id',$apartment_id);
                $statement->bindValue(':floor',$floor);
                $statement->bindValue(':dormitory_id',$dormitory_id);
                $statement->bindValue(':student_id',$student_id);
                $result=$statement->execute();

                $pdo->commit();  
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }
            $statement->closeCursor();

            return $result;
        }
    }
?>